#!/usr/bin/env python3
"""
Minimal Database Migration - Add directory_id to metrics table
Focus on core functionality without complex table replacements
"""

import sqlite3
import sys
from pathlib import Path

def main():
    db_path = Path(__file__).parent.parent / "data" / "uma_insight.db"

    if not db_path.exists():
        print(f"❌ Database not found: {db_path}")
        sys.exit(1)

    print("🚀 Starting minimal database migration...")

    try:
        conn = sqlite3.connect(str(db_path))
        cursor = conn.cursor()

        # Check if migration already done
        cursor.execute("PRAGMA table_info(metrics)")
        columns = [row[1] for row in cursor.fetchall()]

        if 'directory_id' in columns:
            print("✅ Migration already completed!")

            # Verify migration success
            cursor.execute("SELECT COUNT(*) FROM metrics WHERE directory_id IS NOT NULL")
            count = cursor.fetchone()[0]
            print(f"📊 {count} metrics have directory_id assigned")

            # Test directory query
            cursor.execute("""
                SELECT COUNT(*) FROM metrics m
                JOIN directories d ON m.directory_id = d.id
                WHERE d.path = 'tools/metrics/histograms/metadata/accessibility'
            """)
            result = cursor.fetchone()
            if result:
                print(f"📁 Accessibility directory has {result[0]} metrics")

            conn.close()
            return

        # Step 1: Add new columns to metrics table
        print("\n📝 Step 1: Adding columns to metrics table...")
        cursor.execute("ALTER TABLE metrics ADD COLUMN directory_id INTEGER")
        cursor.execute("ALTER TABLE metrics ADD COLUMN filename TEXT")
        print("   ✅ Added directory_id and filename columns")

        # Step 2: Create directory mapping
        print("\n🗂️ Step 2: Creating directory mapping...")

        # Get all unique directory paths from file_path
        cursor.execute("""
            SELECT DISTINCT
                CASE
                    WHEN instr(file_path, '/') > 0
                    THEN substr(file_path, 1, instr(file_path, '/') - 1)
                    ELSE ''
                END as root_dir
            FROM metrics
            WHERE file_path IS NOT NULL AND file_path != ''
            GROUP BY root_dir
        """)
        root_dirs = [row[0] for row in cursor.fetchall() if row[0]]

        print(f"   Found {len(root_dirs)} root directories")

        # For each root directory, get all subdirectories
        all_dirs = {}
        dir_id_map = {}

        # Get directories from existing directories table
        cursor.execute("SELECT id, path FROM directories")
        existing_dirs = cursor.fetchall()
        for dir_id, path in existing_dirs:
            all_dirs[path] = dir_id
            dir_id_map[path] = dir_id

        print(f"   Found {len(existing_dirs)} existing directories")

        # Step 3: Update metrics with directory_id
        print("\n🔄 Step 3: Updating metrics with directory_id...")

        cursor.execute("SELECT id, file_path FROM metrics WHERE file_path IS NOT NULL")
        metrics = cursor.fetchall()

        updated_count = 0
        for metric_id, file_path in metrics:
            # Extract directory and filename
            if '/' in file_path:
                parts = file_path.split('/')
                # Try to find the best matching directory
                for i in range(len(parts), 0, -1):
                    possible_dir = '/'.join(parts[:i])
                    if possible_dir in all_dirs:
                        filename = parts[-1] if i == len(parts) - 1 else '/'.join(parts[i:])

                        cursor.execute("""
                            UPDATE metrics
                            SET directory_id = ?, filename = ?
                            WHERE id = ?
                        """, (all_dirs[possible_dir], filename, metric_id))

                        updated_count += 1
                        break

        print(f"   ✅ Updated {updated_count} metrics")

        # Step 4: Create indexes
        print("\n📊 Step 4: Creating indexes...")
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_metrics_directory_id ON metrics(directory_id)")
        print("   ✅ Created directory_id index")

        # Step 5: Update directories table structure
        print("\n🏗️ Step 5: Enhancing directories table...")

        # Add parent_id and level if they don't exist
        cursor.execute("PRAGMA table_info(directories)")
        dir_columns = [row[1] for row in cursor.fetchall()]

        if 'parent_id' not in dir_columns:
            cursor.execute("ALTER TABLE directories ADD COLUMN parent_id INTEGER")
            cursor.execute("ALTER TABLE directories ADD COLUMN level INTEGER DEFAULT 0")
            print("   ✅ Added parent_id and level columns")

        # Step 6: Verify migration
        print("\n✅ Step 6: Verification...")

        cursor.execute("SELECT COUNT(*) FROM metrics")
        total_metrics = cursor.fetchone()[0]

        cursor.execute("SELECT COUNT(*) FROM metrics WHERE directory_id IS NOT NULL")
        metrics_with_dir = cursor.fetchone()[0]

        cursor.execute("SELECT COUNT(*) FROM directories")
        total_dirs = cursor.fetchone()[0]

        print(f"   Total metrics: {total_metrics}")
        print(f"   Metrics with directory_id: {metrics_with_dir}")
        print(f"   Total directories: {total_dirs}")

        # Test specific directory
        cursor.execute("""
            SELECT COUNT(*) FROM metrics m
            JOIN directories d ON m.directory_id = d.id
            WHERE d.path LIKE '%accessibility%'
        """)
        accessibility_count = cursor.fetchone()[0]
        print(f"   Accessibility-related metrics: {accessibility_count}")

        conn.commit()
        print("\n🎉 Migration completed successfully!")

        conn.close()

    except Exception as e:
        print(f"\n❌ Migration failed: {e}")
        conn.rollback()
        conn.close()
        sys.exit(1)

if __name__ == "__main__":
    main()